Performance Tuning

	4min    ->    45 Min  

SQL Query Tuning  | PLSQL Block Tuning 

Optimizer ->   CBO(11g) | RBO

1) Explain Plan ( Hash #)


	-- To reduce Cost ( I/O | Memory | CPU )

	Scan 
	Join (Nested Loop Join | Hash Join  | Sort Join )

	-->-- Mention

	--sqlplus

	EXPLAIN PLAN FOR 
	SELECT * FROM departments; 

	SELECT * FROM table(dbms_xplan.display());


	SET AUTOT ON;


	-->-- PLSQL (F5)

	-->-- SQL Developer (F10)

					employees_tb      departments_tb
		Nested Loop Join	    High              Low            UNIQUE 
		Hash Join		    High             High  	     No Index   
		Sort Join                   High             High            Non Unique 
									   + ORDER BY 


	2 min			50 Min


	Query Rewriting 


		-->-- Oracle Features 

		IN  |  EXISTS
		Joins 
			e.department_id   = d.department_id
			d.department_id   = e.department_id	L -> R

			70+60    Normal Equi Join
			70+1     (Scalar + Correlated)


		REGULAR Expression
		
		-       -ve Correlated 
		-       -ve GROUP FUNCTION | GROUP BY 

		ANALYTICAL Function 

		GTT (TEMP)


	Index + Hints


		SELECT * FROM employees
                 WHERE employee_id = 100; 

	employee_id   Index available

		--Mention
			user_ind_columns		No	We can Create
							YES     

	SELECT * FROM user_ind_columns WHERE table_name = 'DEPARTMENTS_TB';

	SELECT status FROM user_indexes WHERE index_name = 'DIND_01';   
	-- valid | unusable

	ALTER INDEX DIND_01 UNUSABLE; 

	ALTER INDEX DIND_01 REBUILD;
	ALTER INDEX DIND_01 REBUILD ONLINE;


	DROP INDEX index_name;


	SELECT /*+ INDEX (table_name index_name) */ employee_id, first_name,.... 
	SELECT /*+ NOINDEX (table_name index_name) */ employee_id, first_name,.... 


	Table Partition 
		
		user_tab_partitions  | Exchange 
				LIST
				RANGE	-> IP
				HASH

	S1        s2        s3      s4

		MVIEW 


	stats_gather